SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO



CREATE PROCEDURE [dbo].[usp_StatisticsFacultySearch]
    (
      @param_JOL_Statistics_StartTime VARCHAR(20),
      @param_JOL_Statistics_EndTime VARCHAR(20)
    )
AS
	SET @param_JOL_Statistics_StartTime = @param_JOL_Statistics_StartTime + '-01'
	SET @param_JOL_Statistics_EndTime = @param_JOL_Statistics_EndTime + '-01'
    SELECT 
		Statistics_FacultyID AS JOL_Statistics_FacultyID,
		fdCollName AS JOL_Statistics_FacultyName,
		SUM(Statistics_Lecture) + SUM(Statistics_Recruitment) + SUM(Statistics_CareerTalk) + SUM(Statistics_TheOthers) AS JOL_Statistics_SubTotal,
		SUM(Statistics_Lecture) AS JOL_Statistics_Lecture,
		SUM(Statistics_Recruitment) AS JOL_Statistics_Recruitment,
		SUM(Statistics_CareerTalk) AS JOL_Statistics_CareerTalk,
		SUM(Statistics_TheOthers) AS JOL_Statistics_TheOthers
    FROM  dbo.JOL_Statistics_FacultyActivity INNER JOIN dbo.FS_College ON Statistics_FacultyID = fdCollID
    WHERE @param_JOL_Statistics_StartTime <= Statistics_Date AND Statistics_Date <= @param_JOL_Statistics_EndTime
    GROUP BY Statistics_FacultyID, fdCollName
    ORDER BY JOL_Statistics_SubTotal DESC, JOL_Statistics_Lecture DESC, JOL_Statistics_Recruitment DESC, JOL_Statistics_CareerTalk DESC, JOL_Statistics_TheOthers DESC, fdCollName
	RETURN



GO
